# -*- coding:utf-8 -*-
import os

import cx_Oracle
import xlrd

# 导入指定路径下所有excel数据
# 文件内可包含多级文件夹
# 1.准备python环境
# 2.拷贝文件至指定文件
# 3.修改文件存放位置
# 为提高效率可以将文件存放在多个文件夹，使用多个脚本并行执行
if __name__ == '__main__':
    # 准备环境
    os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
    database_user_password_url = 'ysqx/ysqx@192.168.15.237:1521/ybcp'
    conn = cx_Oracle.connect(database_user_password_url)
    cursor = conn.cursor()

    # 清空数据
    # cursor.execute("truncate table TB_DRSJXX")

    # 文件存放位置
    path = r"F:\器械测试"
    # 1.遍历文件夹下第一层文件和文件夹
    # file_list = os.listdir(path)
    # 2.遍历文件夹下所有文件（包括文件夹包含的文件）
    for home, dirs, files in os.walk(path):
        for filename in files:
            if filename.endswith('.xls') or filename.endswith('.xlsx'):
                print("导入文件-%s" % os.path.join(home, filename))

                # 准备sql语句
                insertDrsjxx = "insert into TB_DRSJXX (DRID, WJMC, DRSJ) values (:1, :2, sysdate)"
                cursor.parse(insertDrsjxx)
                insertDrsjbt = "insert into TB_DRSJBT (BTID,DRID,ZD001,ZD002,ZD003,ZD004,ZD005,ZD006,ZD007," \
                               "ZD008,ZD009,ZD010,ZD011,ZD012,ZD013,ZD014,ZD015,ZD016,ZD017,ZD018,ZD019," \
                               "ZD020,ZD021,ZD022,ZD023,ZD024,ZD025,ZD026,ZD027,ZD028,ZD029,ZD030) " \
                               "values (SEQ_DRSJBT_ID.nextval,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13," \
                               ":14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31)"
                cursor.parse(insertDrsjbt)
                insertDrsjmx = "insert into TB_DRSJMX (MXID,DRID,ZD001,ZD002,ZD003,ZD004,ZD005,ZD006,ZD007," \
                               "ZD008,ZD009,ZD010,ZD011,ZD012,ZD013,ZD014,ZD015,ZD016,ZD017,ZD018,ZD019," \
                               "ZD020,ZD021,ZD022,ZD023,ZD024,ZD025,ZD026,ZD027,ZD028,ZD029,ZD030) " \
                               "values (SEQ_DRSJMX_ID.nextval,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13," \
                               ":14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31)"
                cursor.parse(insertDrsjmx)

                # 1.记录文件
                cursor.execute("SELECT SEQ_DRSJXX_ID.nextval FROM dual")
                drxxid = cursor.fetchone()[0]
                cursor.execute(insertDrsjxx, [drxxid, filename])

                # 读取文件
                file = xlrd.open_workbook(os.path.join(home, filename))
                sheet = file.sheet_by_name('Sheet0')
                titleData = sheet.row_values(0)
                titleList = [None] * 31
                titleList[0] = drxxid
                i = 1
                for item in titleData:
                    titleList[i] = item
                    i += 1
                # 2.记录表头
                cursor.execute(insertDrsjbt, titleList)

                index = 1
                while index < sheet.nrows:
                    row = sheet.row_values(index)
                    dataList = [None] * 31
                    dataList[0] = drxxid
                    i = 1
                    for item in row:
                        # 编码转换:item.encode('iso-8859-1').decode('utf-8')
                        # 长度超4000: '''%s''' % data
                        dataList[i] = '''%s''' % item
                        i += 1
                    # 3.记录数据
                    cursor.execute(insertDrsjmx, dataList)
                    print('导入数据：%s' % row)
                    index += 1
                conn.commit()
                print('更新成功-%s' % filename)

    # 关闭连接和资源
    cursor.close()
    conn.close()
